/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.Sales;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 *
 * @author AnhTuan
 */
public class SalesDAOImpl extends DatabaseConnection implements SalesDAO {

    private List statusList;
    private List paymentModeList;

    public SalesDAOImpl() {
        statusList = new ArrayList();
        statusList.add(new String[]{"NR", "Payment Not Received"});
        statusList.add(new String[]{"BR", "Yet to be Registered"});
        statusList.add(new String[]{"RD", "Registration and Stamp Duty is Done"});

        paymentModeList = new ArrayList();
        paymentModeList.add(new String[]{"1", "Monthly", "The interest charged is 5% of the total."});
        paymentModeList.add(new String[]{"2", "Yearly", "The interest charged is 3% of the total."});
        paymentModeList.add(new String[]{"3", "One Time", ""});
    }

    public Iterator getCountList(int year) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countSalesMonth(?)}");
            cst.setInt(1, year);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                list.add(new int[]{rs.getInt(1), rs.getInt(2)});
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int countAll() {
        try {
            openConnection();
            ResultSet rs = getResultset("Select Count(SaleID) from Sales");
            int result = 0;
            if (rs.next()) {
                result = rs.getInt(1);
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public List getStatusList() {
        return this.statusList;
    }

    public String getStatusName(String status) {
        for (Iterator it = this.statusList.iterator(); it.hasNext();) {
            String[] object = (String[]) it.next();
            if (object[0].equals(status)) {
                return object[1];
            }
        }
        return null;
    }

    public List getPaymentModeList() {
        return this.paymentModeList;
    }

    public String getPaymentModeName(int mode) {
        for (Iterator it = this.paymentModeList.iterator(); it.hasNext();) {
            String[] object = (String[]) it.next();
            int temp = Integer.parseInt(object[0]);
            if (temp == mode) {
                return object[1];
            }
        }
        return null;
    }

    public Iterator<Sales> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From Sales");
            while (rs.next()) {
                Sales row = new Sales();
                row.setSaleID(rs.getInt("SaleID"));
                row.setFlatID(rs.getInt("FlatID"));
                row.setPaymentMode(rs.getInt("PaymentMode"));
                row.setCustomerName(rs.getString("CusName"));
                row.setCustomerAddress(rs.getString("CusAddress"));
                row.setCustomerPhone(rs.getString("CusPhone"));
                row.setSdCharge(rs.getInt("SDCharge"));
                row.setTotalCost(rs.getInt("TotalCost"));
                row.setTotalPayment(rs.getInt("TotalPayment"));
                row.setTotalAmountPaid(rs.getInt("TotalAmountPaid"));
                row.setTotalAmountDue(rs.getInt("TotalAmountDue"));
                row.setSaleStatus(rs.getString("SaleStatus"));
                row.setDescription(rs.getString("Description"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<Sales> getList(String saleId, int paymentMode, String cusName, String cusAddress, String cusPhone, String flatId, String flatName, String createdDateFrom, String createdDateTo, String saleStatus, int page) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageSales(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, saleId);
            cst.setInt(2, paymentMode);
            cst.setString(3, cusName);
            cst.setString(4, cusAddress);
            cst.setString(5, cusPhone);
            cst.setString(6, flatId);
            cst.setString(7, flatName);
            cst.setString(8, createdDateFrom);
            cst.setString(9, createdDateTo);
            cst.setString(10, saleStatus);
            cst.setInt(11, page);
            cst.setInt(12, itemsPerPage);

            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Sales row = new Sales();
                row.setSaleID(rs.getInt("SaleID"));
                row.setFlatID(rs.getInt("FlatID"));
                row.setFlatName(rs.getString("FlatName"));
                row.setPaymentMode(rs.getInt("PaymentMode"));
                row.setCustomerName(rs.getString("CusName"));
                row.setCustomerAddress(rs.getString("CusAddress"));
                row.setCustomerPhone(rs.getString("CusPhone"));
                row.setSaleStatus(rs.getString("SaleStatus"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                row.setPaymentModeName(getPaymentModeName(row.getPaymentMode()));
                row.setStatusName(getStatusName(row.getSaleStatus()));


                list.add(row);
            }
            closeConnection();
            if (list.size() > 0) {
                return list.iterator();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getPageCount(String saleId, int paymentMode, String cusName, String cusAddress, String cusPhone, String flatId, String flatName, String createdDateFrom, String createdDateTo, String saleStatus) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageSales(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, saleId);
            cst.setInt(2, paymentMode);
            cst.setString(3, cusName);
            cst.setString(4, cusAddress);
            cst.setString(5, cusPhone);
            cst.setString(6, flatId);
            cst.setString(7, flatName);
            cst.setString(8, createdDateFrom);
            cst.setString(9, createdDateTo);
            cst.setString(10, saleStatus);
            cst.setInt(11, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Sales getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From Sales Where SaleID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Sales row = new Sales();
            if (rs.next()) {
                row.setSaleID(rs.getInt("SaleID"));
                row.setFlatID(rs.getInt("FlatID"));
                row.setPaymentMode(rs.getInt("PaymentMode"));
                row.setCustomerName(rs.getString("CusName"));
                row.setCustomerAddress(rs.getString("CusAddress"));
                row.setCustomerPhone(rs.getString("CusPhone"));
                row.setSdCharge(rs.getInt("SDCharge"));
                row.setTotalCost(rs.getInt("TotalCost"));
                row.setTotalPayment(rs.getInt("TotalPayment"));
                row.setTotalAmountPaid(rs.getInt("TotalAmountPaid"));
                row.setTotalAmountDue(rs.getInt("TotalAmountDue"));
                row.setSaleStatus(rs.getString("SaleStatus"));
                row.setDescription(rs.getString("Description"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                row.setPaymentModeName(getPaymentModeName(row.getPaymentMode()));
                row.setStatusName(getStatusName(row.getSaleStatus()));

            }
            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int add(Sales object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into Sales(FlatID, PaymentMode, CusName, CusAddress, CusPhone," +
                    "TotalCost, TotalPayment, TotalAmountPaid, TotalAmountDue, SaleStatus, Description, CreatedDate) " +
                    "Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            FlatsDAO flatDao = new FlatsDAOImpl();
            int cost = flatDao.getCost(object.getFlatID());
            int payment = cost;
            if (object.getPaymentMode() == 1) {
                payment *= 1.05;
            } else if (object.getPaymentMode() == 2) {
                payment *= 1.03;
            }
            pst.setInt(1, object.getFlatID());
            pst.setInt(2, object.getPaymentMode());
            pst.setString(3, object.getCustomerName());
            pst.setString(4, object.getCustomerAddress());
            pst.setString(5, object.getCustomerPhone());
            pst.setInt(6, cost);
            pst.setInt(7, payment);
            pst.setInt(8, 0);
            pst.setInt(9, payment);
            pst.setString(10, "NR");
            pst.setString(11, object.getDescription());
            pst.setTimestamp(12, new Timestamp(new java.util.Date().getTime()));
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(Sales object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Sales " +
                    "SET CusName=?, CusAddress=?, CusPhone=?, " +
                    "Description=? Where SaleID=?");
            pst.setString(1, object.getCustomerName());
            pst.setString(2, object.getCustomerAddress());
            pst.setString(3, object.getCustomerPhone());
            pst.setString(4, object.getDescription());
            pst.setInt(5, object.getSaleID());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateToRegisterdSD(int id, int sdCharge) {
        try {
            Sales sale = getRow(id);
            if ("BR".equals(sale.getSaleStatus())) {
                openConnection();
                PreparedStatement pst = conn.prepareStatement("Update Sales Set SaleStatus = ?, SDCharge = ? " +
                        "Where SaleID=?");
                pst.setString(1, "RD");
                pst.setInt(2, sdCharge);
                pst.setInt(3, id);

                int result = pst.executeUpdate();
                closeConnection();
                return result;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete Sales Where SaleID=?");
            pst.setInt(1, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "Delete Sales Where SaleID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
